package utils;


import java.sql.Connection;
import java.sql.DriverManager;
import java.util.ResourceBundle;

import org.hibernate.Session;
import org.hibernate.cfg.AnnotationConfiguration;
import org.hibernate.tool.hbm2ddl.SchemaExport;


public class SessionFactory {
	private static AnnotationConfiguration conf=null;
	private static org.hibernate.SessionFactory factory=null;

	public static Session getSession() throws Exception{
		if (conf == null){
			conf = Anotadas.getAnotadas();
		}

		if(factory == null){
			// Prepara uma f�brica sess�o
			factory  = conf.buildSessionFactory();  
		}

		// Prepara uma sess�o, conex�o com o banco
		return factory.openSession();  

	}

	public static void createSchema(boolean onConsole, boolean onDB) throws Exception{
		if (conf == null){
			conf = Anotadas.getAnotadas();
			//			Properties prop = new Properties();
			//			prop.setProperty("hibernate.dialect", "org.hibernate.dialect.MySQL5Dialect");
			//			prop.setProperty("hibernate.connection.driver_class", "com.mysql.jdbc.Driver");
			//			prop.setProperty("hibernate.connection.url", "jdbc:mysql://localhost:3306/banco");
			//			prop.setProperty("hibernate.connection.username", "root");
			//			prop.setProperty("hibernate.connection.password", ""); 
			//			prop.setProperty("hibernate.show_sql","true");
			//			conf.setProperties(prop);
		}

		/* Verifica se é para criar o schema no banco. Então verifica 
		 * se o database já existe. Para isto, ele utiliza o connection
		 * do session */
		if(onDB){
			ResourceBundle res = ResourceBundle.getBundle("hibernate");

			String className = res.getString("hibernate.connection.driver_class");
			String url = res.getString("hibernate.connection.url");
			String user = res.getString("hibernate.connection.username");
			String password = res.getString("hibernate.connection.password");

			Class.forName(className);

			Connection conn = DriverManager.getConnection(url.replace("cadAuditado", ""), user, password);
			conn.createStatement().execute("DROP DATABASE IF EXISTS cadAuditado;");
			conn.createStatement().execute("CREATE DATABASE cadAuditado;");
			conn.createStatement().execute("USE cadAuditado;");

			SchemaExport export = new SchemaExport(conf);
			export.setOutputFile("SchemaExport.sql");
			export.create(onConsole, onDB);

			//criar triggers e procedures apos o banco ser criado

			
			//insert contato
			conn.createStatement().execute("CREATE TRIGGER totalcontato_after_insert AFTER INSERT ON contato" +
					" FOR EACH ROW BEGIN" +
					"   UPDATE municipio m SET m.totalContatos = m.totalContatos + 1 WHERE m.id = NEW.municipio;" +
					"   INSERT INTO Auditoria (acao, dataHora, descricao, entityId, entityTableName, operador) " +
					"                   VALUES('INSERT', NOW(), '', NEW.id, 'contato', @USUARIO);" +
			" END");

			//delete contato
			conn.createStatement().execute("CREATE TRIGGER contato_after_delete AFTER DELETE ON contato" +
					" FOR EACH ROW BEGIN" +
					"   UPDATE municipio m SET m.totalContatos = m.totalContatos - 1 WHERE m.id = OLD.municipio;"+
					"   INSERT INTO Auditoria (acao, dataHora, descricao, entityId, entityTableName, operador) " +
					"                   VALUES('DELETE', NOW(), '', OLD.id, 'contato', @USUARIO);" +
			" END");

			//update contato
			conn.createStatement().execute("CREATE TRIGGER contato_after_update AFTER UPDATE ON contato" +
					" FOR EACH ROW BEGIN" +
					"   UPDATE municipio m SET m.totalContatos = m.totalContatos + 1 WHERE m.id = NEW.municipio;"+
				    "   UPDATE municipio m SET m.totalContatos = m.totalContatos - 1 WHERE m.id = OLD.municipio;"+
					"   INSERT INTO Auditoria (acao, dataHora, descricao, entityId, entityTableName, operador) " +
					"                   VALUES('UPDATE', NOW(), '', old.id, 'contato', @USUARIO);" +
			" END");

			
			
			//insert telefone
			conn.createStatement().execute("CREATE TRIGGER telefone_after_insert AFTER INSERT ON telefone" +
					" FOR EACH ROW BEGIN" +
					"   INSERT INTO auditoria (acao, dataHora, descricao, entityId, entityTableName, operador) " +
					"                   VALUES('INSERT', NOW(), '', NEW.id, 'telefone', @USUARIO);" +
			" END");

			//delete telefone
			conn.createStatement().execute("CREATE TRIGGER telefone_after_delete AFTER DELETE ON telefone" +
					" FOR EACH ROW BEGIN" +
					"   INSERT INTO Auditoria (acao, dataHora, descricao, entityId, entityTableName, operador) " +
					"                   VALUES('DELETE', NOW(), '', OLD.id, 'telefone', @USUARIO);" +
			" END");

			//update telefone
			conn.createStatement().execute("CREATE TRIGGER telefone_after_update AFTER UPDATE ON telefone" +
					" FOR EACH ROW BEGIN" +
					"   INSERT INTO Auditoria (acao, dataHora, descricao, entityId, entityTableName, operador) " +
					"                   VALUES('UPDATE', NOW(), '', OLD.id, 'telefone', @USUARIO);" +
			" END");
			
			
			//insert municipio
			conn.createStatement().execute("CREATE TRIGGER municipio_after_insert AFTER INSERT ON municipio" +
					" FOR EACH ROW BEGIN" +
					"   INSERT INTO Auditoria (acao, dataHora, descricao, entityId, entityTableName, operador) " +
					"                   VALUES('INSERT', NOW(), '', NEW.id, 'municipio', @USUARIO);" +
			" END");
			
			
			//update municipio
			conn.createStatement().execute("CREATE TRIGGER municipio_after_update AFTER UPDATE ON municipio " +
					"  FOR EACH ROW BEGIN" +
					"    INSERT INTO Auditoria (acao, dataHora, descricao, entityId, entityTableName, operador) " +
					"                   VALUES('UPDATE', NOW(), '', OLD.id, 'municipio', @USUARIO);" +
			"  END");
			
			//delete municipio
			conn.createStatement().execute("CREATE TRIGGER municipio_after_delete AFTER DELETE ON municipio" +
					"  FOR EACH ROW BEGIN" +
					"   INSERT INTO Auditoria (acao, dataHora, descricao, entityId, entityTableName, operador) " +
					"                   VALUES('DELETE', NOW(), '', OLD.id, 'municipio', @USUARIO);" +
			"  END;");

			conn.close();
		}

	}
}
